/****** Object:  StoredProcedure [dbo].[GetProtocolActivityList]    Script Date: 05/22/2008 17:32:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetProtocolActivityList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetProtocolActivityList]

GO

/***************************************************************                          
* Name:   [GetProtocolActivityList]                          
* Purpose:			Retrieves workflow activity list for the user based on the module(Humans or animals)

* @userName			UserName for the user.                                  
* @DomainUserId		FwkDomainUserId of the User                                 
* @protocolNumber	Protocol # if any existing in the search criteria.                                  
* @protocolTitle	Protocol title if any existing in the search criteria.                              
* @piId				Principal investigatoer if any existing in the search criteria.                           
* @moduleType		ModuleType ('H' or 'A')              
*********************************************************************/                          
CREATE PROCEDURE [dbo].[GetProtocolActivityList]
(  
 @UserName varchar(20),  
 @DomainUserId int,  
 @protocolNumber  VARCHAR(12)=null,  
 @protocolTitle VARCHAR(1024)=null,  
 @piId int=null,
 @moduleType char(1)  
)  
AS  
  
BEGIN

 SET NOCOUNT ON 
 
 DECLARE @protocolresourcetypeid varchar(2)  
 DECLARE @hourDiff int  
  
 SET @protocolresourcetypeid = (select [id] from FwkResourceType where [Name] = 'Protocol')  
 SET @hourDiff = (select datediff(hour, getdate(), getutcdate())) * -1  
  
	SELECT   
		  P.[ID] as ProtocolID,  
		  P.ProtocolNumber as ProtocolNumber,   
		  P.Title as ProtocolTitle,   
		  ADMPI.FirstName + ' ' + ADMPI.LastName as PIName,   
		  PP.[ID] as ProtocolProcessID,  
		  type = 
		  case p.ProtocolType
			when 'PROTOCOLTYPE_ATISSUE' then rfc.RefMeaning + ' Tissue'
			else rfc.RefMeaning  
		  end,
		  PP.Type as TypeCode,  
		  PP.Type,   
		  SWAL.ActionRequired as NeededAction,  
		  DATEADD(hh,@hourDiff,SWAL.AssignedOn) as DateAssigned,  
		  isnull(arl.AccessLevel,64) as AccessLevel,  
		  PPWF.ExecutionId  
	FROM  dbo.Protocol P        
	LEFT JOIN dbo.fnAdmQGetAuthorizedResourceList(@DomainUserId, @protocolresourcetypeid) arl  
	ON arl.resourceid = p.fwkresourceid   
    INNER JOIN dbo.protocolProcess pp   
	ON p.[Id] = pp.ProtocolId  
	INNER JOIN dbo.ADMPerson ADMPI   
	ON P.PIPersonID = ADMPI.[Id]  
    INNER JOIN dbo.ProtocolProcess_WorkFlow_Map PPWF  
	ON PP.[ID] = PPWF.ProtocolProcessID  
    INNER JOIN dbo.SWActivityList SWAL  
	ON PPWF.ExecutionID = SWAL.ExecutionID  
    INNER JOIN dbo.SWVirtualUser SWVU  
	ON SWAL.VirtualUserID = SWVU.[ID]  
    INNER JOIN dbo.ADMPerson ADMP  
	ON SWVU.UserID = ADMP.[FWKDomainuserid]  
    INNER JOIN FWKDomainUser FDU    
	ON ADMP.[FWKDomainuserid] = FDU.[Id]    
    INNER JOIN RefCode rfc   
	ON rfc.[RefCode] = pp.Type  
	WHERE p.ModuleType LIKE @moduleType
	AND ISNULL(CAST(p.ProtocolNumber AS VARCHAR(12)),'*')like ISNULL(CAST(@protocolNumber AS VARCHAR(12)), '%')  
	AND ISNULL(CAST(p.Title AS VARCHAR(1024)),'*')like ISNULL(CAST(@protocolTitle AS VARCHAR(1024)) + '%', '%')  
	AND FDU.UserName = @UserName   
	AND SWAL.Status in ('awaiting','acknowledged')
	AND ISNULL(CAST(ADMPI.Id AS VARCHAR(16)), '*')  like ISNULL(CAST(@piId as VARCHAR(16)), '%') 
	AND P.MarkForDeletion = 0 
	ORDER BY SWAL.AssignedOn ASC	--DESC --, p.ProtocolNumber DESC ,p.Title ASC

END

GO

